package com.br.objetos;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;

import com.br.outros.Conexao;
import com.br.outros.DateUtils;
import com.br.outros.MailSenderMobile;

public class VotacaoBarMobile {

	public static boolean terminouVotacao(String telefone) {
		// Statement st = null;
		// ResultSet rs = null;
		Connection con;
		// String URL = "jdbc:mysql://localhost:3306/test";
		// String USE = "root";
		// String PASS = "";
		String driver = Conexao.getDriver();
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();
		String sql = "";
		int contadorGrupo = 0;
		int contadorFinal = 0;
		if (telefone.equals("91594490"))
			System.out.println("PARE AQUI");
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);

			int idCliente = ClienteMobile.getIdByCliente(telefone);
			// PEGA OS GRUPOS QUE O CLIENTE PERTENCE, E QUE TEM CONVITE
			sql = "SELECT IDGRUPOCONVITE AS IDGRUPOCONVITE,  IDGRUPO AS IDGRUPO FROM GRUPOCONVITE WHERE IDGRUPO IN (SELECT IDGRUPO AS IDGRUPO FROM CLIENTEGRUPO WHERE IDCLIENTE = "
					+ idCliente + ");";
			// System.out.println(sql);

			Statement st = null;
			ResultSet rs = null;

			st = con.createStatement();
			rs = st.executeQuery(sql);
			// PEGOU OS GRUPOS DA TABLE GRUPOCONVITE
			while (rs.next()) {
				Integer[] aux = new Integer[2];
				aux[0] = Integer.valueOf(rs.getString("IDGRUPOCONVITE"));
				aux[1] = Integer.valueOf(rs.getString("IDGRUPO"));

				sql = "SELECT * FROM CONVITE WHERE NOTIFICACAO = TRUE AND IDCLIENTE = "
						+ idCliente + " AND IDGRUPOCONVITE = " + aux[0];
				// System.out.println(sql);

				st = null;
				rs = null;

				st = con.createStatement();
				rs = st.executeQuery(sql);
				while (rs.next()) {
					return false;
				}

				sql = "SELECT IDCLIENTE AS IDCLIENTE FROM CLIENTEGRUPO WHERE IDGRUPO = "
						+ aux[1];
				// System.out.println(sql);

				Statement st1 = null;
				ResultSet rs1 = null;

				st1 = con.createStatement();
				rs1 = st1.executeQuery(sql);
				while (rs1.next()) {
					int temp = rs1.getInt("IDCLIENTE");
					sql = "SELECT CONVITEFINALIZADO AS CONVITEFINALIZADO FROM CONVITE WHERE CONVITEFINALIZADO = TRUE AND IDGRUPOCONVITE = "
							+ aux[0] + " AND IDCLIENTE = " + temp + " ;";
					// System.out.println(sql);

					Statement st2 = null;
					ResultSet rs2 = null;

					st2 = con.createStatement();
					rs2 = st2.executeQuery(sql);
					while (rs2.next()) {
						contadorGrupo++;
					}
				}

				sql = "SELECT COUNT(*) AS CONTADORFINAL FROM CLIENTEGRUPO CG "
						+ "JOIN CONVITE C ON (C.IDGRUPOCONVITE = "
						+ aux[0]
						+ ") "
						+ "JOIN GRUPOCONVITE GC ON (GC.IDGRUPOCONVITE = C.IDGRUPOCONVITE AND GC.IDGRUPO = "
						+ aux[1] + ") " + "WHERE CG.IDCLIENTE=" + idCliente
						+ " AND CG.IDGRUPO = " + aux[1] + ";";
				// System.out.println(sql);

				Statement st3 = null;
				ResultSet rs3 = null;

				st3= con.createStatement();
				rs3 = st3.executeQuery(sql);
				while (rs3.next()) {
					contadorFinal = rs3.getInt("CONTADORFINAL");
				}

				if (contadorFinal == contadorGrupo) {
					sql = "UPDATE CONVITE SET NOTIFICACAO = TRUE WHERE IDCLIENTE = "
							+ idCliente
							+ " AND IDGRUPOCONVITE = "
							+ aux[0]
							+ ";";
					Statement stmt = con.createStatement();
					stmt.executeUpdate(sql);
					return true;
				}

			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO VERIFICA CONVITE  - VERIFICA SE TODOS VOTARAM<BR />\n"
					+ e.toString());
			return false;
		}
		return false;
	}


	public static boolean terminouVotacaoBar(String telefone) {
		// Statement st = null;
		// ResultSet rs = null;
		Connection con;
		// String URL = "jdbc:mysql://localhost:3306/test";
		// String USE = "root";
		// String PASS = "";
		String driver = Conexao.getDriver();
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();
		String sql = "";
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);
			sql = "SELECT IDCLIENTE AS IDCLIENTE FROM CLIENTE_CELULAR WHERE TELEFONE = '"
					+ telefone + "'";
			// System.out.println(sql);

			Statement st = null;
			ResultSet rs = null;

			st = con.createStatement();
			rs = st.executeQuery(sql);
			// PEGOU OS GRUPOS DA TABLE GRUPOCONVITE
			int idCli = 0;
			if (rs.next()) {
				idCli = rs.getInt("IDCLIENTE");
			}

			sql = "SELECT * FROM VOTACAOBAR WHERE VOTOU = TRUE AND NOTIFICACAO = FALSE AND IDCLIENTE = "
					+ idCli;
			// System.out.println(sql);

			st = null;
			rs = null;

			st = con.createStatement();
			rs = st.executeQuery(sql);
			// PEGOU OS GRUPOS DA TABLE GRUPOCONVITE
			while (rs.next()) {
				sql = "UPDATE VOTACAOBAR SET NOTIFICACAO = TRUE WHERE IDCLIENTE = "
						+ idCli;
				Statement stmt = con.createStatement();
				stmt.executeUpdate(sql);
				return true;
			}

			con.close();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO VOTACAO BAR MOBILE - TERMINOU VOTO BAR<BR />\n"
					+ e.toString());
			return false;
		}
		return false;
	}

	public static boolean setVoto(String idBar, String idGrupo, String login) {
		String driver = "com.mysql.jdbc.Driver";
		System.out.println("-------------------\n" + "ENVIANDO VOTO BAR \n"
				+ DateUtils.now() + "\n" + "-------------------");
		Connection con;
		// String URL = "jdbc:mysql://127.0.0.1:3306/test";
		// String USE = "root";
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();
		Statement stmt;
		String sql = "";
		try {
			// ClienteMobile cli = new ClienteMobile();
			int idCli = ClienteMobile.getIdByCliente(login);
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);
			stmt = con.createStatement();

			sql = "INSERT INTO VOTACAOBAR (IDBAR, IDCLIENTE, IDGRUPO, STATUS, VOTOU, NOTIFICACAO) VALUES ("
					+ idBar
					+ ","
					+ idCli
					+ ","
					+ idGrupo
					+ ",TRUE,TRUE, FALSE);";
			stmt.executeUpdate(sql);
			// System.out.println(sql);

			sql = "";
			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO VOTACAO MOBILE - SETVOTO<BR />\n"
					+ e.toString());
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERROS VOTACAO MOBILE - SETVOTO<BR />\n"
					+ e.toString());
			return false;
		}
		return true;
	}

	public HashMap<String, ArrayList<BarBeanMobile>> getBarEscolhido() {
		// boolean res = false;
		Statement st = null;
		ResultSet rs = null;
		HashMap<String, ArrayList<BarBeanMobile>> mapTop10 = new HashMap<String, ArrayList<BarBeanMobile>>();
		ArrayList<BarBeanMobile> arrayTop10 = new ArrayList<BarBeanMobile>();

		// String driver = "com.mysql.jdbc.Driver";
		Connection con;
		// String URL = "jdbc:mysql://localhost:3306/test";
		// String USE = "root";
		// String PASS = "";
		String driver = Conexao.getDriver();
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();
		ArrayList<String[]> lista = new ArrayList<String[]>();
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);

			// PARA CADA GRUPO;

			String sql = "SELECT DISTINCT IDGRUPO AS IDGRUPO FROM TEMPDISTANCIAS;";

			st = con.createStatement();
			rs = st.executeQuery(sql);
			ArrayList<Integer> list = new ArrayList<Integer>();
			while (rs.next()) {
				list.add(rs.getInt("IDGRUPO"));
			}

			for (Integer idG : list) {
				sql = "SELECT STATUS FROM TEMPMEDIAS WHERE STATUS = FALSE";

				st = con.createStatement();
				rs = st.executeQuery(sql);
				if (!rs.next()) {
					CalculosMobile v = new CalculosMobile();

					v.insertMedias(idG);
				}
			}
			//

			st = con.createStatement();

			sql = "SELECT IDCLIENTE AS IDCLIENTE FROM CLIENTE_CELULAR WHERE TELEFONE = '";

			// Buscar Alunos
			st = con.createStatement();
			rs = st.executeQuery(sql);

			int idCli = 0;
			while (rs.next()) {
				idCli = rs.getInt("IDCLIENTE");
			}

			sql = "SELECT IDGRUPO AS IDGRUPO FROM CLIENTEGRUPO WHERE IDCLIENTE = "
					+ idCli;

			// Buscar Alunos
			st = con.createStatement();
			rs = st.executeQuery(sql);

			int idGrupo = 0;
			while (rs.next()) {
				idGrupo = rs.getInt("IDGRUPO");
			}

			sql = "SELECT G.APELIDO AS GRUPO, B.IDBAR AS BAR, T.MEDIA AS MEDIA FROM TEMPMEDIAS T JOIN GRUPO G ON (G.IDGRUPO = T.IDGRUPO) JOIN BAR B ON (B.IDBAR = T.IDBAR) WHERE T.IDGRUPO = "
					+ idGrupo + " ORDER BY T.MEDIA LIMIT 10; ";

			// Buscar Alunos
			st = con.createStatement();
			rs = st.executeQuery(sql);

			while (rs.next()) {
				String vet[] = new String[3];
				String grupo = rs.getString("GRUPO");
				String bar = rs.getString("BAR");
				String media = rs.getString("MEDIA");
				vet[0] = grupo;
				vet[1] = bar;
				vet[2] = media;

				lista.add(vet);

			}
			// return id;
			// System.out.println(sql);

			for (String[] aux : lista) {
				BarMobile bar = new BarMobile();
				BarBeanMobile bean = new BarBeanMobile();
				bean = bar.getBar(Integer.valueOf(aux[1]));
				bean.setMedia(Double.valueOf(aux[2]));
				// pegar promocoes por bar
				// descricao - R$ valor
				sql = "SELECT DESCRICAO AS DESCRICAO, VALOR AS VALOR FROM PROMOCAO WHERE DATAVALIDADE <= CURDATE() AND IDBAR = "
						+ aux[1] + "; ";

				// Buscar Alunos
				st = con.createStatement();
				rs = st.executeQuery(sql);
				String res = "";
				while (rs.next()) {
					res += rs.getString("DESCRICAO") + " - R$ "
							+ rs.getDouble("VALOR") + ";";

				}
				bean.setPromocao(res);
				bean.setIdGrupo(idGrupo);
				bean.setAmigos("");
				arrayTop10.add(bean);
			}

			mapTop10.put("arrayBares", arrayTop10);
			con.close();

		} catch (Exception e) {
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO CALCULOS MOBILE - getTop10<BR />\n"
					+ e.toString());
		}
		return mapTop10;
	}

	public static boolean insert() {

		boolean inserido = false;
		Connection conexao = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conexao = DriverManager.getConnection(
					"jdbc:oracle:thin:@localhost:1521:XE", "diego", "12vbd");
			conexao.setAutoCommit(false);
			PreparedStatement stmt = conexao
					.prepareStatement("update votacao_bar set status = 1");
			stmt.executeUpdate();
			stmt.close();
			inserido = true;
		} catch (Exception e) {
			inserido = false;
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO VOTACAO BAR MOBILE - INSERT<BR />\n"
					+ e.toString());
		} finally {
			try {
				if (conexao != null) {
					conexao.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
				MailSenderMobile mail = new MailSenderMobile();
				mail.enviaEmailsErro("ERRO VOTACAO BAR MOBILE - INSERT<BR />\n"
						+ e.toString());
			}
		}
		return inserido;
	}

	public static boolean verificarNotificacao(String telefone,int  idGrupo) {

		// boolean res = false;
		Statement st = null;
		ResultSet rs = null;
		BarBeanMobile bar = new BarBeanMobile();
		// String driver = "com.mysql.jdbc.Driver";
		Connection con;
		// String URL = "jdbc:mysql://localhost:3306/test";
		// String USE = "root";
		// String PASS = "";
		String driver = Conexao.getDriver();
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);
			int idCli = ClienteMobile.getIdByCliente(telefone);
			// PARA CADA GRUPO;
			String sql = "SELECT * FROM TEMPMEDIAS WHERE STATUS = FALSE IDCLIENTE = "+idCli+" AND IDGRUPO = "+idGrupo;

			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				return true;
			}

			con.close();

		} catch (Exception e) {
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO CALCULOS MOBILE - getTop10<BR />\n"
					+ e.toString());
			return false;
		}

		return false;

	}

	public static String contabilizaVotos(int idGrupo) {

		// boolean res = false;
		Statement st = null;
		ResultSet rs = null;
		BarBeanMobile bar = new BarBeanMobile();
		// String driver = "com.mysql.jdbc.Driver";
		Connection con;
		// String URL = "jdbc:mysql://localhost:3306/test";
		// String USE = "root";
		// String PASS = "";
		String driver = Conexao.getDriver();
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);

			// PARA CADA GRUPO;

			String sql = "SELECT IDVOTACAO AS IDVOTACAO, IDBAR AS IDBAR , COUNT(IDBAR) AS VOTOS FROM VOTACAOBAR WHERE STATUS = TRUE AND VOTOU = TRUE GROUP BY IDBAR ORDER BY COUNT(IDBAR) DESC;";

			st = con.createStatement();
			rs = st.executeQuery(sql);
			ArrayList<Integer[]> list = new ArrayList<Integer[]>();
			while (rs.next()) {
				Integer[] vet = new Integer[3];
				vet[0] = rs.getInt("IDBAR");
				vet[1] = rs.getInt("VOTOS");
				vet[2] = rs.getInt("IDVOTACAO");
				list.add(vet);
			}
			// int aux = 0;
			int res = 0;
			System.out.println("-------------------\n"
					+ "CALCULANDO BAR ESCOLHIDO PARA GRUPO \n"
					+ DateUtils.now() + "\n" + "-------------------");
			Integer[] temp = new Integer[list.size()];
			int i = 0;
			for (Integer[] bares : list) {
				temp[i] = bares[1];
				i++;
			}
			Arrays.sort(temp);
			res = temp[0];
			System.out.println(res);
			int barEscolhido = 0;
			for (Integer[] bares : list) {
				if (res == bares[1])
					barEscolhido = bares[0];
			}

			BarMobile barEsc = new BarMobile();
			bar = barEsc.getBar(barEscolhido);
			bar.setPromocao(barEsc.getPromocaoByBar(barEscolhido));
			ArrayList<String> amigosVotaram = ClienteMobile.selectClientesQueVotaram(idGrupo);
			String amigosConcat = "";
			for (String amigo : amigosVotaram) {
				amigosConcat += amigo + ";";
			}
			bar.setAmigos(amigosConcat);

			for (Integer[] bares : list) {

				Statement stmt = con.createStatement();

				sql = "UPDATE VOTACAOBAR SET NOTIFICACAO = TRUE WHERE IDVOTACAO = "
						+ bares[2];

				// con.commit();
				stmt.executeUpdate(sql);
			}

			con.close();

		} catch (Exception e) {
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO CALCULOS MOBILE - getTop10<BR />\n"
					+ e.toString());
		}

		return BarMobile.serialize(bar);
	}

}
